package org.dictionary;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.MatrixCursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.provider.BaseColumns;
import android.text.TextUtils;
import android.util.Log;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

/**
 *
 * @author siarhei
 */
public class DictionaryDao {
    private static final String DATABASE_NAME = "flashcards";
    private static final int DATABASE_VERSION = 1;
    public static final String DICTIONARY_TABLE_NAME = "dictionary";

    private DictionaryHelper dictionaryHelper;
    private Context context;

    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private static DictionaryDao instance;
    
    public static DictionaryDao getInstance(Context context) {
        if (instance == null) {
            instance = new DictionaryDao(context);
        }
        return instance;
    }

    private DictionaryDao(Context context) {
        this.dictionaryHelper = new DictionaryHelper(context);
        this.context = context;
    }

    public Cursor getCursor(String textPattern) {
        return getCursor(textPattern, null);
    }

    public Cursor getCursor(String textPattern, Map<String, String> projectionMap) {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setProjectionMap(projectionMap);
        qb.setTables(DICTIONARY_TABLE_NAME);

        String orderBy = DictionaryEntry.ORIGINAL_TEXT + " ASC";
        String selection = null;
        if (!TextUtils.isEmpty(textPattern)) {
            selection = DictionaryEntry.ORIGINAL_TEXT + " LIKE '%" + textPattern + "%'";
        }

        SQLiteDatabase db = dictionaryHelper.getReadableDatabase();
        return qb.query(db, null, selection, null, null, null, orderBy);
    }

    public DictionaryEntry get(Long id) {
        String selection = BaseColumns._ID + '=' + id;
        Cursor c = dictionaryHelper.getReadableDatabase().query(DICTIONARY_TABLE_NAME, null, selection, null, null, null, null);
        if (c.moveToFirst()) {
            DictionaryEntry ret = new DictionaryEntry();
            ret.setId(c.getLong(c.getColumnIndex(DictionaryEntry._ID)));
            ret.setOriginal(c.getString(c.getColumnIndex(DictionaryEntry.ORIGINAL_TEXT)));
            ret.setTranslation(c.getString(c.getColumnIndex(DictionaryEntry.TRANSLATED_TEXT)));
            ret.setBatch(c.getInt(c.getColumnIndex(DictionaryEntry.BATCH)));
            try {
                String lastUpdate = c.getString(c.getColumnIndex(DictionaryEntry.LAST_UPDATE));
                if (lastUpdate != null) {
                    ret.setLastUpdate(dateFormat.parse(lastUpdate));
                }
            } catch (ParseException ex) {
                Log.d("DEBUG", "error occurs while parsing a date: " + ex.getMessage());
            }
            return ret;
        } else {
            return null;
        }
    }

    public Cursor getQuizEntries() {
        String[] columnNames = new String[]{DictionaryEntry._ID, 
                                            DictionaryEntry.BATCH,
                                            DictionaryEntry.ORIGINAL_TEXT,
                                            DictionaryEntry.TRANSLATED_TEXT};
        MatrixCursor ret = new MatrixCursor(columnNames);

        for (int i = 7; i >= 0; i--) {
            String selection = DictionaryEntry.BATCH + "=" + i;
            Date expiredArg = new Date();
            long delay = i * 86400000;
            expiredArg.setTime(expiredArg.getTime() - delay);
            selection += " AND " + DictionaryEntry.LAST_UPDATE  + " < '" + dateFormat.format(expiredArg) + "'";
            SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
            qb.setTables(DICTIONARY_TABLE_NAME);
            SQLiteDatabase db = dictionaryHelper.getReadableDatabase();
            Cursor cur = qb.query(db, null, selection, null, null, null, null);
            cur.moveToFirst();
            while (!cur.isAfterLast()) {
                ret.addRow(new Object[]{cur.getLong(cur.getColumnIndex(DictionaryEntry._ID)),
                                        cur.getInt(cur.getColumnIndex(DictionaryEntry.BATCH)),
                                        cur.getString(cur.getColumnIndex(DictionaryEntry.ORIGINAL_TEXT)),
                                        cur.getString(cur.getColumnIndex(DictionaryEntry.TRANSLATED_TEXT))});
                cur.moveToNext();
            }
        }
        return ret;
    }

    private int getBatchSize(int batch) {
        SQLiteDatabase db = dictionaryHelper.getReadableDatabase();
        Cursor batchCur = db.rawQuery("select count(*) from dictionary where " + DictionaryEntry.BATCH + " = " + batch, null);
        batchCur.moveToFirst();
        int ret = batchCur.getInt(0);
        batchCur.close();
        return ret;
    }

    private int getExpiredWordNumber() {
        int ret = 0;
        for (int i = 7; i >= 0; i--) {
            String selection = DictionaryEntry.BATCH + "=" + i;
            Date expiredArg = new Date();
            long delay = i * 86400000;
            expiredArg.setTime(expiredArg.getTime() - delay);
            selection += " AND " + DictionaryEntry.LAST_UPDATE  + " < '" + dateFormat.format(expiredArg) + "'";
            
            SQLiteDatabase db = dictionaryHelper.getReadableDatabase();
            Cursor cur = db.rawQuery("select count(*) from dictionary where " + selection, null);
            cur.moveToFirst();
            ret += cur.getInt(0);
            cur.close();
        }
        return ret;
    }

    public Cursor getStatistics() {
        String[] columnNames = new String[]{Statistics.DICTIONARY_SIZE, Statistics.EXPIRED_SIZE, Statistics.BATCH0, Statistics.BATCH1, Statistics.BATCH2, Statistics.BATCH3,
                                            Statistics.BATCH4, Statistics.BATCH5, Statistics.BATCH6, Statistics.BATCH7};
        MatrixCursor ret = new MatrixCursor(columnNames);

        SQLiteDatabase db = dictionaryHelper.getReadableDatabase();
        Cursor wordCountCur = db.rawQuery("select count(*) from dictionary", null);
        wordCountCur.moveToFirst();
        Integer wordCount = wordCountCur.getInt(0);
        wordCountCur.close();

        Integer batch0 = getBatchSize(0);
        Integer batch1 = getBatchSize(1);
        Integer batch2 = getBatchSize(2);
        Integer batch3 = getBatchSize(3);
        Integer batch4 = getBatchSize(4);
        Integer batch5 = getBatchSize(5);
        Integer batch6 = getBatchSize(6);
        Integer batch7 = getBatchSize(7);

        ret.addRow(new Object[] {wordCount, getExpiredWordNumber(), batch0, batch1, batch2, batch3, batch4, batch5, batch6, batch7});
        return ret;
    }

    public void delete(Long id) {
        SQLiteDatabase db = dictionaryHelper.getWritableDatabase();
        db.delete(DICTIONARY_TABLE_NAME, DictionaryEntry._ID + "=" + id, null);
    }

    public void save(DictionaryEntry de) {
        Log.d(getClass().getSimpleName(), "saving dictionary entry");
        ContentValues values = new ContentValues();        
        values.put(DictionaryEntry.ORIGINAL_TEXT, de.getOriginal());
        values.put(DictionaryEntry.TRANSLATED_TEXT, de.getTranslation());
        values.put(DictionaryEntry.BATCH, de.getBatch());
        if (de.getLastUpdate() != null) {
            values.put(DictionaryEntry.LAST_UPDATE, dateFormat.format(de.getLastUpdate()));
        }

        SQLiteDatabase db = dictionaryHelper.getWritableDatabase();
        Long rowId;
        if (de.getId() == null) {
            values.put(DictionaryEntry.BATCH, 0);
            values.put(DictionaryEntry.LAST_UPDATE, dateFormat.format(new Date()));
            rowId = db.insert(DICTIONARY_TABLE_NAME, DictionaryEntry.ORIGINAL_TEXT, values);
            if (rowId == 0) {
                throw new SQLException("Failed to insert row into ");
            }
        } else {
            db.update(DICTIONARY_TABLE_NAME, values, DictionaryEntry._ID + "= " + de.getId(), null);            
        }
    }

    private static class DictionaryHelper extends SQLiteOpenHelper {

        private static final String DICTIONARY_TABLE_CREATE =
                    "CREATE TABLE " + DICTIONARY_TABLE_NAME + " ("
                    + DictionaryEntry._ID + " INTEGER PRIMARY KEY,"
                    + DictionaryEntry.ORIGINAL_TEXT + " TEXT,"
                    + DictionaryEntry.TRANSLATED_TEXT + " TEXT,"
                    + DictionaryEntry.BATCH + " INT,"
                    + DictionaryEntry.LAST_UPDATE + " DATETIME);";

        public DictionaryHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DICTIONARY_TABLE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                Log.w(DictionaryHelper.class.getName(), "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
                db.execSQL("DROP TABLE IF EXISTS " + DICTIONARY_TABLE_CREATE);
                onCreate(db);
        }
    }

}
